/**
 * 
 */
package com.toncen.samepms.fisher.exporter;

import java.io.File;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.sky.common.string.StringUtils;
import com.sky.fisher.exporter.AbstractExporter;
import com.sky.fisher.formatter.AbstractFormatter;
import com.sky.fisher.model.CheckboxColumnModel;
import com.sky.fisher.model.CommonColumnModel;
import com.sky.fisher.model.GridModel;
import com.sky.fisher.model.PositionColumnModel;
import com.sky.fisher.query.PageResult;
import com.sky.fisher.service.FisherService;

/**
 * Excel导出器
 * 
 * @author tch
 * 
 */
public class ExcelExporter extends AbstractExporter {
	/**
	 * 
	 */
	private String formateValue(String value) {
		String regex = "<SPAN STYLE='COLOR:([\\s\\S]*)'>([\\s\\S]*)</SPAN>";
		if (!StringUtils.isEmpty(value) && value.toUpperCase().matches(regex)) {
			int spanStartIndex = value.toUpperCase().indexOf("<SPAN STYLE='COLOR:");
			if (spanStartIndex != -1) {
				spanStartIndex = value.toUpperCase().indexOf("'>", spanStartIndex);
				if (spanStartIndex != -1) {
					spanStartIndex = spanStartIndex + "'>".length();
					int spanEndIndex = value.toUpperCase().indexOf("</SPAN>", spanStartIndex);
					if (spanEndIndex != -1) {
						value = value.substring(spanStartIndex, spanEndIndex);
					}
				}
			}
		}
		return value;
	}
	/**
	 * 重写父类方法
	 * @param grid
	 * @param paramters
	 * @return
	 * @throws Exception
	 */
	public static List<String> getExportColumnList(final GridModel grid, Map<String, String[]> paramters) throws Exception {
		String includeColumns = paramters.get(FisherService.PARAMETER_INCLUDE_COLUMNS) == null
				|| paramters.get(FisherService.PARAMETER_INCLUDE_COLUMNS)[0] == null ? null : paramters.get(FisherService.PARAMETER_INCLUDE_COLUMNS)[0];
		String excludeColumns = paramters.get(FisherService.PARAMETER_EXCLUDE_COLUMNS) == null
				|| paramters.get(FisherService.PARAMETER_EXCLUDE_COLUMNS)[0] == null ? null : paramters.get(FisherService.PARAMETER_EXCLUDE_COLUMNS)[0];
		String[] includeColumn = StringUtils.isEmpty(includeColumns) ? null : includeColumns.split(FisherService.SEPARATOR_EXPORT_COLUMN, -1);
		String[] excludeColumn = StringUtils.isEmpty(excludeColumns) ? null : excludeColumns.split(FisherService.SEPARATOR_EXPORT_COLUMN, -1);
		List<String> exportColumnList = new ArrayList<String>();
		List<String> includeColumnList = new ArrayList<String>();
		List<String> excludeColumnList = new ArrayList<String>();
		if (includeColumn != null) {
			for (String column : includeColumn) {
				includeColumnList.add(column);
			}
		}
		if (excludeColumn != null) {
			for (String column : excludeColumn) {
				excludeColumnList.add(column);
			}
		}
		for (CommonColumnModel column : grid.getColumnList()) {
			if ("false".equalsIgnoreCase(column.getVisual())) {
				continue;
			}
			if (column instanceof CheckboxColumnModel) {
				continue;
			}
			if (includeColumn == null) {
				includeColumnList.add(column.getName());
			}
			if (includeColumnList.contains(column.getName()) && !excludeColumnList.contains(column.getName())) {// 鍖呭惈鏈夎�屾帓闄や腑娌掓湁鐨勫瓧娈�
				exportColumnList.add(column.getName());
			}
		}
		return exportColumnList;
	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.sky.fisher.exporter.AbstractExporter#export(com.sky.fisher.model.GridModel,
	 *      java.io.File, java.lang.String)
	 */
	public File export(final GridModel grid, Map<String, String[]> parameters, String strSQL) throws Exception {
		File exportFile = File.createTempFile(String.valueOf(System.currentTimeMillis()), ".xls.temp");// 创建临时导出文件
		exportFile.deleteOnExit();// 虚拟机关闭的时候删除
		logger.debug("创建临时文件【" + exportFile + "】...");
		WritableWorkbook workbook = null;
		try {
			String title = parameters.get(FisherService.PARAMETER_TITLE) == null || parameters.get(FisherService.PARAMETER_TITLE).length == 0 ? "" : parameters
					.get(FisherService.PARAMETER_TITLE)[0];
			List<String> exportColumnList = getExportColumnList(grid, parameters);
			workbook = Workbook.createWorkbook(exportFile);
			WritableSheet sheet = workbook.createSheet(StringUtils.isEmpty(title) ? "暂无" : title, 0);
			PageResult pageResult = null;
			String value = null;
			AbstractFormatter formatter = null;
			int page = 1;
			int pagesize = 100;
			WritableCell cell = null;
			int rowIndex = 0;// 行索引
			int cellIndex = 0;// 列索引
			// 表格标题的格式
			if (!StringUtils.isEmpty(title)) {
				cellIndex = 0;
				WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 16, WritableFont.BOLD);
				WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
				titleFormat.setAlignment(Alignment.CENTRE);
				titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
				int totalCell = 0;
				for (CommonColumnModel column : grid.getColumnList()) {
					if (!exportColumnList.contains(column.getName())) {// 不要导出的字段不添加到结果集中
						continue;
					}
					totalCell++;
				}
				cell = new Label(cellIndex, rowIndex, title, titleFormat);
				sheet.addCell(cell);
				sheet.mergeCells(cellIndex, rowIndex, totalCell - 1, rowIndex);
				rowIndex++;
			}
			// 单元格标题的格式
			cellIndex = 0;
			WritableFont columnTitleFont = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD);
			WritableCellFormat columnTitleFormat = new WritableCellFormat(columnTitleFont);
			columnTitleFormat.setAlignment(Alignment.CENTRE);
			columnTitleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			for (CommonColumnModel column : grid.getColumnList()) {
				if (!exportColumnList.contains(column.getName())) {// 不要导出的字段不添加到结果集中
					continue;
				}
				sheet.setColumnView(cellIndex, Integer.parseInt(column.getWidth()) / 6);
				cell = new Label(cellIndex, rowIndex, column.getDisplay(), columnTitleFormat);
				sheet.addCell(cell);
				cellIndex++;
			}
			rowIndex++;
			// 内容的格式
			int fromtRowIndex = rowIndex - 1;
			WritableFont contentFont = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD);
			WritableCellFormat contentRightFormat = new WritableCellFormat(contentFont);
			contentRightFormat.setAlignment(Alignment.RIGHT);
			WritableCellFormat contentLeftFormat = new WritableCellFormat(contentFont);
			contentLeftFormat.setAlignment(Alignment.LEFT);
			WritableCellFormat contentCenterFormat = new WritableCellFormat(contentFont);
			contentCenterFormat.setAlignment(Alignment.CENTRE);
			CellFormat cellFormat;
			boolean hasSumRow = FisherService.hasSumRow(grid);// 是否有合计行
			boolean hasScaleColumn = FisherService.hasScaleColumn(grid);// 是否有比例列
			Map<CommonColumnModel, String> columnSumMap = new HashMap<CommonColumnModel, String>(5);// 合计行的数据
			String temp;
			do {
				pageResult = FisherService.executeQuery(grid, strSQL, page, pagesize);
				if (hasSumRow || hasScaleColumn) {// 有合计行或者比例列
					for (CommonColumnModel column : grid.getColumnList()) {
						if (!FisherService.isSumColumn(column) && !FisherService.isScaleColumn(column)) {// 不是合计列并且不是比例列
							continue;
						}
						temp = "0";
						if (columnSumMap.get(column) != null) {// 已经计算过
							temp = columnSumMap.get(column);
						}
						columnSumMap.put(column, new BigDecimal(temp).add(new BigDecimal(FisherService.getTotal(column, pageResult))).toString());
					}
				}
				for (Map<String, String> rowResult : pageResult.getResult()) {
					cellIndex = 0;
					for (CommonColumnModel column : grid.getColumnList()) {
						if (!exportColumnList.contains(column.getName())) {// 不要导出的字段不添加到结果集中
							continue;
						}
						value = rowResult.get(column.getName().toUpperCase());
						if (!StringUtils.isEmpty(value) && !StringUtils.isEmpty(column.getFormatter())) {
							formatter = FisherService.getFormatter(column.getFormatter());
							value = formatter.format(column, value);
							 value=formateValue(value);
						}
						if (column instanceof PositionColumnModel) {// 索引列
							value = String.valueOf(rowIndex - fromtRowIndex);
						}
						if ("right".equalsIgnoreCase(column.getAlign())) {// 居右
							cellFormat = contentRightFormat;
						} else if ("left".equalsIgnoreCase(column.getAlign())) {// 居左
							cellFormat = contentLeftFormat;
						} else {// 居中
							cellFormat = contentCenterFormat;
						}
						cell = new Label(cellIndex, rowIndex, value, cellFormat);
						sheet.addCell(cell);
						cellIndex++;
					}
					rowIndex++;
				}
				page = pageResult.getPage() + 1;
				pageResult.getResult().clear();// 释放内存
			} while (pageResult.getTotal() / pageResult.getPagesize() > pageResult.getPage()// 还有下一页
					|| (pageResult.getTotal() / pageResult.getPagesize() == pageResult.getPage() && pageResult.getTotal() % pageResult.getPagesize() != 0));// 最后一页
			if (hasScaleColumn) {// 有比例列，更新比例列
				cellIndex = 0;
				String total;
				for (CommonColumnModel column : grid.getColumnList()) {
					if (!exportColumnList.contains(column.getName())) {// 不要导出的字段不添加到结果集中
						continue;
					}
					if (!FisherService.isScaleColumn(column)) {
						continue;
					}
					total = columnSumMap.get(column);
					if (!FisherService.isSumColumn(column)) {// 非合计列，比例列使用完后移除合计
						columnSumMap.remove(column);
					}
					for (int i = 2; i <= rowIndex; i++) {
						value = sheet.getCell(exportColumnList.indexOf(column.getName()), i).getContents();
						value = StringUtils.replaceAll(value, "%", "");
						value = FisherService.getColumnScale(column, value, total);
						if (!StringUtils.isEmpty(value) && !StringUtils.isEmpty(column.getFormatter())) {
							formatter = FisherService.getFormatter(column.getFormatter());
							value = formatter.format(column, value);
						}
						if ("right".equalsIgnoreCase(column.getAlign())) {// 居右
							cellFormat = contentRightFormat;
						} else if ("left".equalsIgnoreCase(column.getAlign())) {// 居左
							cellFormat = contentLeftFormat;
						} else {// 居中
							cellFormat = contentCenterFormat;
						}
						cell = new Label(exportColumnList.indexOf(column.getName()), i, value, cellFormat);
						sheet.addCell(cell);
						cellIndex++;
					}
				}
			}
			if (hasSumRow) {// 有合计行，导出合计行
				cellIndex = 0;
				for (CommonColumnModel column : grid.getColumnList()) {
					if (!exportColumnList.contains(column.getName())) {// 不要导出的字段不添加到结果集中
						continue;
					}
					value = columnSumMap.get(column);
					if (!StringUtils.isEmpty(value) && !StringUtils.isEmpty(column.getFormatter())) {
						formatter = FisherService.getFormatter(column.getFormatter());
						value = formatter.format(column, value);
					}
					if (column instanceof PositionColumnModel) {// 索引列
						value = "合计";
					}
					if ("right".equalsIgnoreCase(column.getAlign())) {// 居右
						cellFormat = contentRightFormat;
					} else if ("left".equalsIgnoreCase(column.getAlign())) {// 居左
						cellFormat = contentLeftFormat;
					} else {// 居中
						cellFormat = contentCenterFormat;
					}
					cell = new Label(cellIndex, rowIndex, value, cellFormat);
					sheet.addCell(cell);
					cellIndex++;
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			if (workbook != null) {
				workbook.write();
				workbook.close();
			}
		}
		return exportFile;
	}
}
